import sys, os, re
from itertools import izip
import cardsharp as cs
import MySQLdb
from contextlib import closing
from itertools import count, izip
from xlrd.xldate import *
from util import key_as_str
from configuration import config
def _reverse_file(path):
print 'reversing file...'
with closing(open(path, 'rb')) as f:
lines = f.readlines()
with closing(open(path, 'wb')) as f:
lines.reverse()
f.writelines([l for l in lines[:-1]])
print 'reverse complete.'
[docs]def make_snttext_wvfbi_xwalk(in_path = os.path.join(config.rule_dir, 'sentence_crosswalks', 'wvfbi'),
out_path = os.path.join(config.rule_dir, 'process_rules_2', 'sentence', 'snttextx', 'wvfbi')):
in_ds = cs.load(source=os.path.join(in_path, '2_or_more.xls'), format = 'excel')
out_ds = cs.Dataset(['key', 'value', ('priority', 'integer')])
in_ds.variables['value:cdsp'].convert('integer')
in_ds.variables['value:cnf'].convert('integer')
out_ds.add_row([':vars:','cdsp|cnf|incmax|incmin|suspmax|suspmin|prb'])
out_ds.add_row([':keys:','cdisptextx'])
out_ds.add_row([':lookup:','dsp|cnf'])
for _c, row in izip(count(1), in_ds):
key = row['key:cdisptextx'].lower().strip()
out = '|'.join([str(row['value:cdsp'])if row['value:cdsp'] else '',
str(row['value:cnf']) if row['value:cnf'] else '',
row['value:incmax'] or '',
row['value:incmin'] or '',
row['value:suspmax'] or '',
row['value:suspmin'] or '',
row['value:prb'] or '']).replace('=', '')
if '#' in key or '#' in out or '(' in out:
in_val = ''.join([':re:math-e:', key])
elif '#' in row['key:cdisptextx']:
in_val = ''.join([':re:exact:', key])
else:
in_val = key
out_ds.add_row([in_val, out, _c])
out_ds.save(source=os.path.join(out_path, 'crosswalk_2_or_more.txt'), format='text', overwrite=True, no_escape=True)
cs.wait()
_reverse_file(os.path.join(out_path, 'crosswalk_2_or_more.txt'))
print '...2_or_more complete...'
print '...loading single...'
out_ds = cs.Dataset(['key', 'value'])
out_ds.add_row([':vars:','cdsp|pjp'])
out_ds.add_row([':keys:','cdisptextx'])
out_ds.add_row([':lookup:','dsp|pjp'])
single_path = os.path.join(in_path, 'single.xls')
for ds in cs.list_datasets(source=single_path):
in_ds = cs.load(source=single_path, format = 'excel', dataset=ds)
in_ds.variables['value:cdsp'].convert('integer')
in_ds.variables['value:pjp'].convert('integer')
for row in in_ds:
key = row['key:cdisptextx'].lower().strip()
out = '|'.join([str(row['value:cdsp']), str(row['value:pjp'])])
out_ds.add_row([key, out])
out_ds.save(source=os.path.join(out_path, 'crosswalk_single.txt'), format='text', overwrite=True, no_escape=True)
cs.wait()
_reverse_file(os.path.join(out_path, 'crosswalk_single.txt'))
print 'single complete...'
def _get_tables():
tbl_names = []
with closing(MySQLdb.connect(user='root', passwd='pass')) as cnx:
with closing(cnx.cursor()) as c:
c.execute('SHOW TABLES FROM %s' % config.db_stand_name)
tbl_names = [table[0] for table in c.fetchall()]
return tbl_names
_in_key_re = re.compile('(?<=key:).+?(?=($|\())')
_out_var_re = re.compile('value:|\(skip\)')
def _get_out_vars(ds):
return [_out_var_re.sub('', v.name) for v in ds.variables if v.name.startswith('value:')]
def _get_in_keys(ds):
return [_in_key_re.search(v.name).group() for v in ds.variables if v.name.startswith('key:')]
def _convert_out_vals(in_ds, lookup):
#TODO we need to be able to load math rules
skip_convert = False
for row in in_ds:
if row[0] == ':all:':
return
for var in in_ds.variables:
if var.name.startswith('value:'):
if var.name.replace('value:', '')[1:] in lookup or var.name.replace('value:','') in lookup:
in_ds.variables[var.name].convert('integer')
#function to replace .0 in string values (excel load issue)
regex = re.compile('^\d+?\.0$')
def _re_replace(value, no_replace):
if no_replace:
return value.replace('(blank-space)', ' ')
if regex.match(value):
#print 'replacing .0'
value = value.replace('.0', '')
#replace (blank-space) with ' '
return value.replace('(blank-space)', ' ')
else:
return value.replace('(blank-space)', ' ')
[docs]def create_prediction_crosswalks(state, no_replace):
#get tables to construct lookup rule
output_path = os.path.join(config.rule_dir, 'process_rules_2')
input_file = os.path.join(config.rule_dir, 'offense_crosswalks',
'regular_state', '%s_pred.xls' % state)
out_state = re.search('regular_state\\\\(.+)_pred.xls', str(input_file)).groups()[0]
tbl_names = _get_tables()
#create one crosswalk for each worksheet in xwalk dataset
for ds in cs.list_datasets(source=input_file):
#load xwalk dataset
in_ds = cs.load(source=input_file, format='excel', dataset=ds)
in_ds.variables['priority'].convert('integer')
priority = 0
#create the output datasets
out_ds_1 = cs.Dataset(['key', 'value', ('priority', 'integer')])
out_ds_2 = cs.Dataset(['key', 'value', ('priority', 'integer')])
#get the output vars rule
out_vars = _get_out_vars(in_ds)
in_keys = _get_in_keys(in_ds)
lookup = []
#create the :vars: rule and add to the dataset
if out_vars:
out_ds_1.add_row([':vars:', '|'.join(['a%s' % v for v in out_vars])])
out_ds_2.add_row([':vars:', '|'.join(['c%s' % v for v in out_vars])])
out_ds_1.add_row([':keys:', '|'.join(['a%s' % v for v in in_keys])])
out_ds_2.add_row([':keys:', '|'.join(['c%s' % v for v in in_keys])])
#create the lookup rule and add to the dataset
for v in out_vars:
lookup.append(v)
if out_vars:
out_ds_1.add_row([':lookup:', '|'.join([v for v in out_vars])])
out_ds_2.add_row([':lookup:', '|'.join([v for v in out_vars])])
#convert to output values to integers
_convert_out_vals(in_ds, lookup)
in_ds.wait()
#add data to the datasets
for row in in_ds:
key = []
value = []
#iterate over variables to gather key : value rule mapping
for var in in_ds.variables:
var_name = re.sub('value:|key:', '', var.name).lower()
_var = row.get(var.name)
#create key
if var_name not in out_vars and var_name != 'priority':
v = key_as_str(row[var.name])
key_value = ''.join([':re:search:', v]) if v != ':all:' else v
key_value = re.sub('blank|(space)|(blank-space)|(blank_space)', ' ', key_value)
key.append(_re_replace(key_value, no_replace))
#create vlaue
elif var_name != 'priority' and not re.search('remove',ds):
#todo remove this replace
value.append(_re_replace(str(row[var.name]), no_replace) if row[var.name] is not None else '')
#create priority
#priority += 1
priority = (row[var_name]) if (var_name == 'priority' and row[var_name]) else 1
if key:
out_ds_1.add_row(['|'.join(key), '|'.join(value), priority])
out_ds_2.add_row(['|'.join(key), '|'.join(value), priority])
#save the datasets
for k,v in [(out_ds_1, r'arrest\ancic\%s\crosswalk_%s.txt' % (out_state, ds)),
(out_ds_2, 'sentence\cncic\%s\crosswalk_%s.txt' % (out_state, ds))]:
k.save(source=os.path.join(output_path, v), format='text', overwrite=True, no_escape=True)
cs.wait()
with open(os.path.join(output_path, '%s' % v), 'rb') as f:
lines = f.readlines()
with open(os.path.join(output_path, '%s' % v), 'wb') as f:
lines.reverse()
f.writelines([l for l in lines[:-1]])
if out_state == 'ne':
out_state = 'nb'
for k,v in [(out_ds_1, r'arrest\ancic\%s\crosswalk_%s.txt' % (out_state, ds)),
(out_ds_2, 'sentence\cncic\%s\crosswalk_%s.txt' % (out_state, ds))]:
k.save(source=os.path.join(output_path, v), format='text', overwrite=True, no_escape=True)
cs.wait()
with open(os.path.join(output_path, '%s' % v), 'rb') as f:
lines = f.readlines()
with open(os.path.join(output_path, '%s' % v), 'wb') as f:
lines.reverse()
f.writelines([l for l in lines[:-1]])
_loc_dict = {('state', 'achgsvr'): lambda state: '/process_rules_1/arrest/achgsvr/crosswalk_%s.txt' % state,
('state', 'cchgsvr'): lambda state: '/process_rules_1/sentence/cchgsvr/crosswalk_%s.txt' % state,
('state', 'ancic'): lambda state: '/process_rules_2/arrest/ancic/%s/crosswalk_%s_1.txt' % (state, state),
('state', 'cncic'): lambda state: '/process_rules_2/sentence/cncic/%s/crosswalk_%s_1.txt' % (state, state),
('state', 'ainc'): lambda state: '/process_rules_2/arrest/ancic/%s/crosswalk_inc_1.txt' % state,
('state', 'cinc'): lambda state: '/process_rules_2/sentence/cncic/%s/crosswalk_inc_1.txt' % state,
('state', 'ainc1'): lambda state: '/process_rules_2/arrest/ancic/%s/crosswalk_inc_1.txt' % state,
('state', 'cinc1'): lambda state: '/process_rules_2/sentence/cncic/%s/crosswalk_inc_1.txt' % state,
('state', 'acdv'): lambda state: '/process_rules_2/arrest/ancic/%s/crosswalk_cdv_1.txt' % state,
('state', 'ccdv'): lambda state: '/process_rules_2/sentence/cncic/%s/crosswalk_cdv_1.txt' % state,
('state', 'ainc2'): lambda state: '/process_rules_2/arrest/ancic/%s/crosswalk_inc_2.txt' % state,
('state', 'cinc2'): lambda state: '/process_rules_2/sentence/cncic/%s/crosswalk_inc_2.txt' % state,
('state', 'adom'): lambda state: '/process_rules_2/arrest/ancic/%s/crosswalk_dom_1.txt' % state,
('state', 'cdom'): lambda state: '/process_rules_2/sentence/cncic/%s/crosswalk_dom_1.txt' % state,
('wvfbi', 'ancic'): lambda state: '/process_rules_2/arrest/ancic/wvfbi/crosswalk_%s.txt' % state,
('wvfbi', 'cncic'): lambda state: '/process_rules_2/sentence/cncic/wvfbi/crosswalk_%s.txt' % state,
('state', 'nonarr'): lambda state: '/process_rules_3/arrest/nonarr/crosswalk_%s_1.txt' % state,
('state', 'nonarr1'): lambda state: '/process_rules_3/arrest/nonarr/crosswalk_%s_1.txt' % state,
('state', 'nonarr2'): lambda state: '/process_rules_3/arrest/nonarr/crosswalk_%s_2.txt' % state,
('state', 'noncrt'): lambda state: '/process_rules_3/sentence/noncrt/crosswalk_%s_1.txt' % state,
('state', 'noncrt1'): lambda state: '/process_rules_3/sentence/noncrt/crosswalk_%s_1.txt' % state,
('state', 'noncrt2'): lambda state: '/process_rules_3/sentence/noncrt/crosswalk_%s_2.txt' % state,
('wvfbi', 'nonarr'): lambda state: '/process_rules_3/arrest/nonarr/wvfbi/crosswalk_%s_1.txt' % state,
('wvfbi', 'noncrt'): lambda state: '/process_rules_3/sentence/noncrt/wvfbi/crosswalk_%s_1.txt' % state,
('state', 'adsp'): lambda state: '/process_rules_2/arrest/adsp/crosswalk_%s.txt' % state,
('state', 'cdsp'): lambda state: '/process_rules_2/sentence/cdsp/crosswalk_%s.txt' % state,
('state', 'acnt'): lambda state: '/process_rules_2/arrest/acnt/%s/crosswalk_%s.txt' % (state, state),
('state', 'ccnt'): lambda state: '/process_rules_2/sentence/ccnt/%s/crosswalk_%s.txt' % (state, state),
('wvfbi', 'adsp'): lambda state: '/process_rules_2/arrest/adsp/wvfbi/crosswalk_%s.txt' % state,
('wvfbi', 'cdsp'): lambda state: '/process_rules_2/sentence/cdsp/wvfbi/crosswalk_%s.txt' % state,
}
#add cdsp locations
for i in xrange(1, 100):
_loc_dict[('state', 'cdsp%i' % i)] = lambda args: '/process_rules_2/sentence/cdsp/%s/crosswalk_%s_%i.txt' % (args[1], args[1], args[0])
[docs]def load_all_crosswalk(input_file,output_path, type, no_replace):
#get tables to construct lookup rule
tbl_names = _get_tables()
state = re.search('(regular_state|wvfbi)\\\\(.+).xls', input_file).groups(1)[1]
#create one crosswalk for each worksheet in xwalk dataset
for ds in cs.list_datasets(source=input_file):
#load dataset
in_ds = cs.load(source=input_file, format='excel', dataset=ds)
out_ds_1 = cs.Dataset(['key', 'value', 'priority'])
out_ds_2 = cs.Dataset(['key', 'value', 'priority'])
#get the output vars
out_vars = _get_out_vars(in_ds)
in_key = _get_in_keys(in_ds)
lookup = []
priority = ''
offense_ds = re.search('offense', ds)
#create :lookup: rule
for v in out_vars:
if v[1:] in tbl_names or (re.search('offense', ds) and v in tbl_names) or v in ['nonarr', 'noncrt']:
if v[1:] not in ['nonarr', 'noncrt'] and v not in ['nonarr', 'noncrt'] :
l_v = v
elif re.search('offense|ancic|cncic', ds):
l_v = v
else:
l_v = 'admrec'
lookup.append(l_v[1:] if not re.search('offense|nonarr|noncrt', ds) else l_v) #assign admrec if not an offense crosswalk #todo this is dirty fix this
#convert to output values to integers
_convert_out_vals(in_ds, lookup)
in_ds.wait()
lookup = [':lookup:', '|'.join(lookup)] if lookup else None
#create the :vars: rule
out_vars_rule = [':vars:', '|'.join(out_vars)] if out_vars else None
out_vars_rule_a = [':vars:', '|'.join(['a%s' % v for v in out_vars])] if out_vars else None
out_vars_rule_s = [':vars:', '|'.join(['c%s' % v for v in out_vars])] if out_vars else None
#create the :keys: rule
in_keys_rule = [':keys:', '|'.join(in_key)]
in_keys_rule_a = [':keys:', '|'.join(['a%s' % v for v in in_key])]
in_keys_rule_s = [':keys:', '|'.join(['c%s' % v for v in in_key])]
#create the skip_missing rule
skip_missing = []
for v in in_ds.variables:
if re.search('(skip)', v.name):
skip_missing.append(re.sub('value:|\(skip\)', '', v.name))
#create the key transform rule
transform = []
for v in in_ds.variables:
if re.search('replace|sub', v.name):
v_name = _in_key_re.search(v.name).group()
t = re.search('(replace|sub):(.+?):(.+?)(?<!\\\\)\)', v.name).groups()
transform.append((v_name, t[0], t[1], t[2]))
#create the :skip_missing: rule
skip_rule = [':skip_missing:', '|'.join(skip_missing)]
skip_rule_a = [':skip_missing:', '|'.join(['a%s' % v for v in skip_missing])]
skip_rule_s = [':skip_missing:', '|'.join(['c%s' % v for v in skip_missing])]
#create the :key: rules
transform_rules, transform_rules_a, transform_rules_s = [], [], []
for t in transform:
transform_rules.append([':key:\t%s\t:%s:\t%s'% (t[0], t[1], t[2]), t[3]])
transform_rules_a.append([':key:\ta%s\t:%s:\t%s'% (t[0], t[1], t[2]), t[3]])
transform_rules_s.append([':key:\tc%s\t:%s:\t%s'% (t[0], t[1], t[2]), t[3]])
#add the :vars: and :keys: and :lookup: rules to the dataset
if re.search('offense', ds):
if out_vars_rule_a and not re.search('none', ''.join(out_vars_rule_a)):#make sure we do not have none for a key (delete crosswalk)
out_ds_1.add_row(out_vars_rule_a)
out_ds_2.add_row(out_vars_rule_s)
out_ds_1.add_row(in_keys_rule_a)
out_ds_2.add_row(in_keys_rule_s)
if skip_missing:
out_ds_1.add_row(skip_rule_a)
out_ds_2.add_row(skip_rule_s)
if transform_rules_a:
for t in transform_rules_a:
out_ds_1.add_row(t)
for t in transform_rules_s:
out_ds_2.add_row(t)
if lookup:
out_ds_2.add_row(lookup)
else:
if out_vars_rule and not re.search('none', ''.join(out_vars_rule)): #make sure we do not have none for a key (delete crosswalk)
out_ds_1.add_row(out_vars_rule)
out_ds_1.add_row(in_keys_rule)
if skip_missing:
out_ds_1.add_row(skip_rule)
if transform_rules:
for t in transform_rules:
out_ds_1.add_row(t)
if lookup:
out_ds_1.add_row(lookup)
#TODO figure out which variables are integers
for row in in_ds:
key = []
sentence_key = []
value = []
#iterate over variables to gather key : value ruel mapping
_rule_type = ''
for var in in_ds.variables:
var_name = re.sub('value:|key:', '', var.name).lower()
_var = row.get(var.name)
#do not add (blank) rule
if _var == '(blank)':
continue
#create key
if var_name not in out_vars and var_name != 'priority' and not re.search('skip', var_name):
if var_name == 'rule_type':
if row['rule_type'] is not None:
_rule_type = row['rule_type']
continue
if _rule_type:
key_value = '%s%s' % (_rule_type, key_as_str(row[var.name]))
_rule_type = ''
else:
key_value = key_as_str(row[var.name])
#this is deprecated remove in future
if '(re)' in var_name and '#' in key_value:
key_value = ':re:search:%s' % key_value
#if (c) in key only add to sentence_key
if '(c)' in var_name:
sentence_key.append(_re_replace(key_value, no_replace))
#otherwise update both keys
else:
key.append(_re_replace(key_value, no_replace))
sentence_key.append(_re_replace(key_value, no_replace))
#create vlaue
elif var_name != 'priority':
value.append(_re_replace(str(row[var.name]), False) if row[var.name] is not None else '')
priority = str(row[var_name]) if (var_name == 'priority' and row[var_name]) else '1'
if key:
out_ds_1.add_row(['|'.join(key), '|'.join(value), priority])
if offense_ds:
out_ds_2.add_row(['|'.join(sentence_key), '|'.join(value), priority])
#save the datasets
if offense_ds:
offense_num = re.search('offense(\d+)', ds)
offense_num = offense_num.groups()[0] if offense_num else ''
if offense_num:
_loc_dict[('state', 'ancic%s' % offense_num)] = lambda info: '/process_rules_2/arrest/ancic/%s/crosswalk_%s_%s.txt' % (info[0], info[0], info[1])
_loc_dict[('state', 'cncic%s' % offense_num)] = lambda info: '/process_rules_2/sentence/cncic/%s/crosswalk_%s_%s.txt' % (info[0], info[0], info[1])
_loc_dict[('wvfbi', 'ancic%s' % offense_num)] = lambda info: '/process_rules_2/arrest/ancic/wvfbi/crosswalk_%s.txt' % info[0]
_loc_dict[('wvfbi', 'cncic%s' % offense_num)] = lambda info: '/process_rules_2/sentence/cncic/wvfbi/crosswalk_%s.txt' % info[0]
for k,v in [(out_ds_1, 'ancic.txt'), (out_ds_2, 'cncic.txt')]:
k.variables['priority'].convert('integer')
if offense_num:
temp_out = config.rule_dir + _loc_dict[(type, '%s%s' %(v[:5], offense_num))]((state, offense_num if not re.search('remove', ds) else '%s_remove' % offense_num))
else:
temp_out = config.rule_dir + _loc_dict[(type, v[:5])](state)
if not temp_out:
temp_out = os.path.join(output_path, v)
k.save(source=temp_out, format='text', overwrite=True, no_escape=True)
cs.wait()
_reverse_file(temp_out)
#handle the ne/nb state
if state=='ne':
temp_out = temp_out.replace('ne', 'nb')
k.save(source=temp_out, format='text', overwrite=True, no_escape=True)
cs.wait()
_reverse_file(temp_out)
else:
#handle cdsp cases
_c = re.search('cdsp(\d+)', ds)
if 'cdsp' in ds and _c:
temp_out = _loc_dict.get((type, ds), lambda s: False)((int(_c.groups()[0]), state))
if temp_out and not os.path.isfile(config.rule_dir + temp_out):
print 'adding new state cdsp crosswalk. Makes sure to update state cdsp rule controller.'
else:
temp_out = _loc_dict.get((type, ds), lambda s: False)(state)
if not temp_out:
temp_out = os.path.join(output_path, '%s.txt' % ds)
else:
temp_out = config.rule_dir + temp_out
out_ds_1.variables['priority'].convert('integer')
out_ds_1.save(source=temp_out, format='text', overwrite=True, no_escape=True)
cs.wait()
_reverse_file(temp_out)
if state=='ne':
temp_out = temp_out.replace('ne', 'nb')
out_ds_1.save(source=temp_out, format='text', overwrite=True, no_escape=True)
cs.wait()
_reverse_file(temp_out)
[docs]def load_offense_rules(state, no_replace):
try:
if state != 'fed':
load_all_crosswalk(r'%s\offense_crosswalks\regular_state\%s.xls' % (config.rule_dir, state),
config.rule_dir + '/crosswalk_raw/state/%s' % state, 'state', no_replace)
load_all_crosswalk(r'%s\offense_crosswalks\wvfbi\%s.xls' % (config.rule_dir, state),
config.rule_dir + '/crosswalk_raw/wvfbi/%s' % state, 'wvfbi', no_replace)
except (XLDateAmbiguous, XLDateError), e:
print "Unable to load. Make sure that you do not have date values stored in your excel file. Select all cells and format them as text. %s" % str(e)
sys.exit(1)
def _move_ncic(type, region, file, path):
import shutil
if region == 'ne':
_file = re.sub('(a|c)ncic(\d+)', 'crosswalk_nb_\\2', file)
shutil.copy(path,
os.path.join(config.rule_dir, config.offense_dir,
'sentence' if type == 'c' else 'arrest',
'%sncic' % type, 'nb', _file)
)
_file = re.sub('(a|c)ncic(\d+)', 'crosswalk_%s_\\2' % region, file)
shutil.move(path,
os.path.join(config.rule_dir, config.offense_dir,
'sentence' if type == 'c' else 'arrest',
'%sncic' % type, region, _file)
)
[docs]def load_rules(region, wvfbi, no_replace):
if wvfbi:
print 'Creating wvfbi snttext crosswalks...'
make_snttext_wvfbi_xwalk()
print 'wvfbi snttext crosswalks written.'
if region:
_dir = os.path.join(config.rule_dir, 'crosswalk_raw')
print 'loading %s rules' % region
for folder in ('state', 'wvfbi'):
_out_path = os.path.join(_dir, folder, region)
if not os.path.isdir(_out_path):
os.makedirs(_out_path)
if region == 'ne':
_out_path = os.path.join(_dir, folder, 'nb')
if not os.path.isdir(_out_path): os.makedirs(_out_path)
_out_path = os.path.join(_dir, folder, 'ne')
if region != 'fed': create_prediction_crosswalks(region, no_replace)
print 'prediction crosswalks added'
load_offense_rules(region, no_replace)
print 'offense crosswalks added'
for folder in ('state', 'wvfbi'):
_out_path = os.path.join(_dir, folder, region)
files = os.listdir(_out_path)
if folder == 'state':
for file in files:
if file.startswith('cncic'):
_move_ncic('c', region, file, os.path.join(_out_path, file))
elif file.startswith('ancic'):
_move_ncic('a', region, file, os.path.join(_out_path, file))
if len(os.listdir(_out_path)) == 0: os.rmdir(_out_path)
else: print 'Not all crosswalks able to save. Check crosswalk_raw folder.'
cs.wait()